PostgreSQL pg_bulkload 控制文件
1 背景知识
本文介绍数据加载中所使用的控制文件中的相关参数。在控制文件中指定以下参数，可以控制导入和导出数据的各种行为。
在控制文件中可以使用 # 作为注释。
2 通用参数
2.1 TYPE
- 参数语法。
TYPE = CSV | BINARY | FIXED | FUNCTION
- 参数说明。
此参数用于指定导入数据的类型。默认值为 CSV。
- CSV: 导入CSV 格式的文本文件数据。
- BINARY | FIXED : 导入二进制文件的数据。
- FUNCTION： 导入函数所生成的数据。
2.2 INPUT | INFILE
- 参数语法。
INPUT | INFILE = path | stdin | [ schemaname. ] function_name (argvalue, ...)
- 参数说明。
此参数可以指定导入的数据来源和位置。此值必须填写。根据 TYPE 参数值，此参数值可能如下：
- 一个服务器上的文件：
当指定INPUT=path时，表示 pg_bulkload 通过本地文件导入数据。path可以指定相对路径和绝对路径。并且 PostgreSQL 用户必须能够读取此文件的权限。此方式只能在“TYPE=CSV” 或者 “TYPE=BINARY” 时可用。 - 一个标准输入(stdin)：
当指定INPUT=stdin时，表示 pg_bulkload 通过标准输入（stdin） 导入数据。此方式只能在“TYPE=CSV” 或者 “TYPE=BINARY” 时可用。
简单示例
pg_bulkload csv_load.ctl < DATA.csv
- 一个SQL 函数：
当指定INPUT=FUNCTION时，表示 pg_bulkload 通过SQL 函数导入数据。此方式只能在“TYPE=FUNCTION” 时可用。
参数示例
下面是一个函数和一个控制文件的例子。这里创建了一个 sample_type 数据类型。并使用 sample_function 函数生成数据用于加载到 sample_table 表中。请参考 pg_buklload 装载FUNCTION 函数数据。
CREATE TYPE sample_type AS (sum integer, name char(10));
CREATE FUNCTION sample_function() RETURNS SETOF sample_type
AS $ SELECT id1 + id2, upper(name) FROM INPUT_TABLE $
LANGUAGE SQL;
TABLE = sample_table
TYPE = FUNCTION
WRITER = DIRECT
INPUT = sample_function() # if to use the user-defined function
# 3 #INPUT = generate_series(1, 1000) # if to use the build-in function, which generate sequential numbers from 1 to 1000
2.3 WRITER | LOADER
- 参数语法。
WRITER | LOADER = DIRECT | BUFFERED | BINARY | PARALLEL
- 参数说明。
此参数可以指定导出的数据类型。默认为DIRECT。
- DIRECT : 绕过共享缓冲区，并跳过WAL日志，导出数据至表中。这是此参数默认的方式。
- BUFFERED：通过共享缓冲区，并记录WAL 日志，导出数据到表中，这种方式和 copy 命令的性能相似。
- BINARY: 将数据导出为二进制文件，此选项常用于 导出二进制数据文件，并将二进制数据作为 二进制数据导入来源源。
- PARALLEL： 与
WRITER=DIRECT相同，并在此基础上开启并发。如果数据库设置了密码验证，请配置密码文件 .pgpass 。
2.4 OUTPUT | TABLE
- 参数语法。
OUTPUT | TABLE = { [ schema_name. ] table_name | outfile }
- 参数说明。
此参数指定导出数据所存放的目的地。此参数必填。选项如下：
- **将表作为目的地：**指定存放导入数据的表名称。如果省略
schema_name则使用search_path中第一个匹配的表。 - **将文件作为目的地：**指定导出数据的文件名称。
2.5 SKIP | OFFSET
- 参数语法。
SKIP | OFFSET = n
- 参数说明。
此参数指定跳过导入数据的行数。此参数设置时，不能同时指定TYPE=FUNCTION和SKIP参数。
2.6 LIMIT | LOAD
- 参数语法。
LIMIT | LOAD = n
- 参数说明。
限制导入数据的行数。默认值为无限制，即导入所有数据。可以同时指定TYPE=FUNCTION参数。
2.7 ENCODING
- 参数语法。
ENCODING = encoding
- 参数说明。
此参数指定装载数据的字符集。并检查指定的字符集是否有效，并在需要的时候将装载数据的字符集转换为数据库字符集。默认情况下即不验证也不转换输入数据的字符集。
如果确定输入的数据和数据库是同一编码时，则可以不指定此参数，这样可以直接跳过字符集验证和转换，从而缩短加载时间。
请注意，只有INPUT 为 stdin 时，才默认使用 client_enconding 作为输入数据的编码。不得同时指定 TYPE=FUNCTION 和 ENCODING 两个参数。
请参考 Built-in Conversions 获取有效的编码名称，以下是选项值和对应的效果。
| SQL_ASCII(DB encoding) | non-SQL_ASCII(DB encoding) | |
|---|---|---|
| not specified | 不检查，不转换 | 不检查，不转换 |
| SQL_ASCII | 不检查，不转换 | 仅检查 |
| non-SQL_ASCII, same as DB | 仅检查 | 仅检查 |
| non-SQL_ASCII, different from DB | 仅检查 | 检查并转换 |
2.8 FILTER
- 语法说明。
FILTER = [ schema_name. ] function_name [ (argtype, ... ) ]
- 参数说明。
此参数用于指定导入数据文件中每一行的过滤函数。只要函数名称在数据库中是唯一的，就可以省略 argtype 的定义。如果不指定，数据将会原模原样的导入目标表。
更多详情，请参考如何编写FILETER 函数。
此选项 FILTER 和 TYPE=FUNCTION 不得同时使用。
2.9 CHECK_CONSTRAINTS
- 语法说明。
CHECK_CONSTRAINTS = YES | NO
- 参数说明。
此参数用于指定导入数据期间是否启用检查约束。默认为不检查NO。此选项CHECK_CONSTRAINTS和WRITER=BINARY不允许同时使用。
2.10 PARSE_ERRORS
- 语法说明。
PARSE_ERRORS = n
- 参数说明。
此参数用于忽略在解析、字符集检查、字符集转换、FILTER 函数、CHECK 约束检查、NOT NULL 检查或数据类型转换过程中出错的元组数量。
导入出错的元组将不会记录到 PARSE BADFILE 。
以下是对这个参数不同情况的说明。
| 值 | 说明 |
|---|---|
| >0 的整数 | 允许出错的元组超过这个数值，这时将会停止导入数据。 已经导入的数据会提交，未导入的数据将不会导入。 |
| 0 | 不允许任何错误出现，如果出现将会停止导入数据。 |
| -1 或 INFINITE | 允许所有的出错的元组数量为无限。 |
2.11 DUPLICATE_ERRORS
- 语法说明。
DUPLICATE_ERRORS = n
- 参数说明。
此参数用于指定违反唯一键约束报错的数量。发生冲突的元组将会记录在 DUPLICATE BADFILE 中。此参数 DUPLICATE_ERRORS 和 WRITER=BINARY 不允许同时设置。
以下是对这个参数不同情况的说明。
| 值 | 说明 |
|---|---|
| >0 的整数 | 允许出错的元组超过这个数值，这时将会停止导入数据。已经导入的数据会提交，未导入的数据将不会导入。 |
| 0 | 不允许任何错误出现，如果出现错误将会停止导入数据。 |
| -1 或 INFINITE | 允许所有的出错的元组数量为无限。 |
2.12 ON_DUPLICATE_KEEP
- 参数语法。
ON_DUPLICATE_KEEP = NEW | OLD
- 参数说明。
指定违反唯一键时，插入的新数据被删除还是旧数据被删除。删除的数据将会被记录在 DUPLICATE BADFILE 文件中。
如果启用该选项，还需要将 DUPLICATE_ERRORS 参数设置为大于零。
此参数 ON_DUPLICATE_KEEP 和 WRITER=BINARY 参数不能够同时设置。
以下是对这个参数不同情况的说明。
NEW ： 默认值为NEW 。表示新数据被保留，旧数据被删除。换句话说存在违规元组时，保留最后一个。
OLD： 表示旧数据将会被保留，新数据将会被删除。
2.13 LOGFILE
- 参数语法。
LOGFILE = path
- 参数说明。
写入日志结果的路径。可以为相对路径和绝对路径。
默认值为 $PGDATA/pg_bulkload/<_timestamp_>_<_dbname_>_<_schema_>_<_table_>.log。
2.14 PARSE_BADFILE
- 参数语法。
PARSE_BADFILE = path
- 参数说明。
此参数用于记录指定解析、字符编码检查、字符编码转换、FILTER函数、CHEKC 约束检查、NOT NULL 检查或者数据类型转换过程中出错的无效记录的文件路径。
默认值为 $PGDATA/pg_bulkload/<_timestamp_>_<_dbname_>_<_schema_>_<_table_>.bad.<_extension-of-infile_>
2.15 DUPLICATE_BADFILE
- 参数语法。
DUPLICATE_BADFILE = path
- 参数说明。
此参数用于记录指定违反唯一键的元组的无效记录的文件记录。此文件格式为CSV格式。
不得同时指定 WRITER=BINARY 和 DUPLICATE_BADFILE。
默认值为：$PGDATA/pg_bulkload/<_timestamp_>_<_dbname_>_<_schema_>_<_table_>.dup.csv
2.16 TRUNCATE
- 参数语法。
TRUNCATE = YES | NO
- 参数说明。
YES: 则使用TRUNCATE命令删除目标表中所有的记录。
NO: 则无动作。
不得同时指定 WRITER=BINARY 和 TRUNCATE 两个选项。
2.17 VERBOSE
- 参数语法。
VERBOSE = YES | NO
- 参数说明。
指定报错元组是否写入日志（LOGFILE）文件中。 YES 将会写入日志文件。默认值为NO。
2.18 MULTI_PROCESS
- 参数语法。
MULTI_PROCESS = YES | NO
- 参数说明。
此参数指定是否使用多线程进行数据导入，导出和解析。
- 如果为YES时，将启用多线程。
- 如果为NO时，将启用单线程。
默认值为 NO。如果已设置 WRITER=PARALLEL，则忽略 MULTI_PROCESS。
如果数据库设置了密码验证，则需要配置 .pgpass 密码文件。更多请见 限制。
启用 MULTI_PROCESS 后，请确保没有其他 PostgreSQL 后端进程同时修改表的模式。因为这会导致读取进程和写入进程看到的模式不同，从而报错。
3 CSV 导入参数
3.1 DELIMITER
- 参数语法。
DELIMITER = delimiter_character
-
参数说明。
指定CSV 文件的文件分隔符，默认为逗号（，）。如果想要加载制表符（tab）分隔文件（TSV）时，你可以指定DELIMITER为tab并用双引号。 -
简单示例。
DELIMITER=" " # a double-quoted tab
你还可以实用选项指定 DELIMITER 使用 -o $'\t' .
pg_bulkload tsv.ctl -o
### 3.2 QUOTE
1. 参数语法。
```yml
QUOTE = quote_character
- 参数说明。
指定引号的字符。默认为双引号（“）。
3.3 ESCAPE
- 参数语法。
ESCAPE = escape_character
- 参数说明。
指定什么字符作为引号。，默认为双引号（“）。
3.4 NULL
- 参数语法。
NULL = null_string
- 参数说明。
表示空值的字符串。默认为空，注意此参数值不带引号。
3.5 FORCE_NOT_NULL
- 参数语法。
FORCE_NOT_NULL = column
- 参数说明。
指定每一列都为非空值处理。此选项FORCE_NOT_NULL和FILTER不能同时使用。
4 Binary 导出参数
4.1 OUT_COL 参数语法
OUT_COL = type [ (size) ] [ NULLIF { 'null_string' | null_hex } ]
4.2 OUT_COL 参数说明
对导出文件中每一列进行数据定义。定义由类型名称、偏移量和长度（以字节为单位） 组成。char和 varchar表示导入的数据为文本数据，否则，就是二进制数据。如果是二进制数据，服务器和数据文件的字节序必须一样。
4.2.1 CHAR | CHARACTER :
固定长度的字符串，必须指定长度。如果存储的字符串比声明的长度短，值将会被空格填充。在控制文件中可以设置为 COL=CHAR(size) 。
4.2.2 VARCHAR | CHARACTER VARYING :
固定长度的字符串，必须指定长度。如果存储的字符串比声明的长度短，值将会被空格填充。在控制文件中可以设置为 COL=VARCHAR(size)。
4.2.3 SMALLINT | SHOFT :
有符号整数。长度为2个字节的。
4.2.4 INTEGER | INT :
有符号整数。长度为 2 或 4 或 8 字节. 默认为4字节。
4.2.5 BIGINT | LONG :
有符号整数。长度为8个字节。
4.2.6 UNSIGNED SMALLINT | SHORT :
无符号整数，长度为2 个字节。
4.2.7 UNSIGNED INTEGER | INT :
无符号整数，长度为2个字节或4个字节。默认值为4个字节。
4.2.8 FLOAT | REAL :
浮点数。长度为4个字节或者8个字节。默认长度为4个字节。
4.2.9 *DOUBLE :
- 浮点数。长度为8个字节。
NULL 字符串可以使用以下方式指定。如果省略之后又要导入 NULL 值。 NULL 将会被记录在 PARSE_BADFILE。
4.2.10 NULLIF 'null_string' :
指定 CHAR 或者 VARCHAR 表示 NULL 的字符串。字符串的长度必须与类型的长度相同。
4.2.11 NULLIF null_hex :
指定不是 CHAR 和 VARCHAR 表示 NULL 的十六进制值。十六进制的值的长度必须与类型的长度相同。
5 Binary 导入参数
5.1 COL 参数格式
COL = type [ (size) ] [ NULLIF { 'null_string' | null_hex } ]
5.2 COL 参数说明
对导入文件的每一列的定义，定义包括：类型名称、偏移量和长度（以字节为单位）组成。
CHAR 和 VARCHAR 表示导入文本数据。否则，就是二进制数据。如果二进制数据，服务器和数据文件的大小端必须一致。
5.2.1 CHAR | CHARACTER :
指定截断尾部空格后的字符串。
5.2.2 VARCHAR | CHARACTER VARYING :
指定保留尾部空格的字符串。
5.2.3 SMALLINT | SHOFT :
2个字节的有符号整数。
5.2.4 INTEGER | INT :
有符号整数，可以为2个字节，4个字节，8个自己。默认为4 个字节。
5.2.5 BIGINT | LONG :
8个字节的有符号整数。
5.2.6 UNSIGNED SMALLINT | SHORT :
2 个字节的无符号整数。
5.2.7 UNSIGNED INTEGER | INT :
无符号整数，可以为2 个字节，4个字节无符号整数。默认为4.
5.2.8 FLOAT | REAL :
浮点数，可以为4个字节或8个字节。默认为4。
5.2.9 DOUBLE :
浮点数，可以为8个字节。
5.3 指定类型长度和偏移量
类型的长度和偏移量可以按照以下方式指定：
- TYPE : TYPE with default length follows.
- TYPE(L) : TYPE with L bytes follows.
- TYPE(S+L) : L bytes, offset S bytes from the beginning of the line
- TYPE(S:E) : start at S bytes and end at E bytes.
可以使用以下方式指定NULL 字符串。
5.3.1 NULLIF 'null_string' :
指定 CHAR 或者 VARCHAR 表示 NULL 的字符串。字符串的长度必须与类型的长度相同。
5.3.2 NULLIF null_hex :
指定不是 CHAR 和 VARCHAR 表示 NULL 的十六进制值。十六进制的值的长度必须与类型的长度相同。
此外，还提供了 COL N ，与 COL CHAR(N) 相同。以便向后兼容。
此参数设置为YES时，将 COL N 视为 COL CHAR(N)。此参数设置为 NO 时视为 COL VARCHAR(N)。
PRESERVE_BLANKS = YES | NO
此参数设置一行的长度，用于保证一行不会过大。
STRIDE = n
6 参考链接
- pg_bulkload (ossc-db.github.io)DELIMITER=\t'
### 3.2 QUOTE
1. 参数语法。
{{CODE_BLOCK_24}}
2. 参数说明。
指定引号的字符。默认为双引号（“）。
### 3.3 ESCAPE
1. 参数语法。
{{CODE_BLOCK_25}}
2. 参数说明。
指定什么字符作为引号。，默认为双引号（“）。
### 3.4 NULL
1. 参数语法。
{{CODE_BLOCK_26}}
2. 参数说明。
表示空值的字符串。默认为空，注意此参数值不带引号。
### 3.5 FORCE_NOT_NULL
1. 参数语法。
{{CODE_BLOCK_27}}
2. 参数说明。
指定每一列都为非空值处理。此选项 `FORCE_NOT_NULL` 和 `FILTER` 不能同时使用。
## 4 Binary 导出参数
### 4.1 OUT_COL 参数语法
{{CODE_BLOCK_28}}
### 4.2 OUT_COL 参数说明
对导出文件中每一列进行数据定义。定义由类型名称、偏移量和长度（以字节为单位） 组成。char和 varchar表示导入的数据为文本数据，否则，就是二进制数据。如果是二进制数据，服务器和数据文件的字节序必须一样。
#### 4.2.1 **CHAR | CHARACTER :**
固定长度的字符串，必须指定长度。如果存储的字符串比声明的长度短，值将会被空格填充。在控制文件中可以设置为 `COL=CHAR(size)` 。
#### 4.2.2 **VARCHAR | CHARACTER VARYING :**
固定长度的字符串，必须指定长度。如果存储的字符串比声明的长度短，值将会被空格填充。在控制文件中可以设置为 `COL=VARCHAR(size)`。
#### 4.2.3 **SMALLINT | SHOFT :**
有符号整数。长度为2个字节的。
#### 4.2.4 **INTEGER | INT :**
有符号整数。长度为 2 或 4 或 8 字节. 默认为4字节。
#### 4.2.5 **BIGINT | LONG :**
有符号整数。长度为8个字节。
#### 4.2.6 **UNSIGNED SMALLINT | SHORT :**
无符号整数，长度为2 个字节。
#### 4.2.7 **UNSIGNED INTEGER | INT :**
无符号整数，长度为2个字节或4个字节。默认值为4个字节。
#### 4.2.8 **FLOAT | REAL :**
浮点数。长度为4个字节或者8个字节。默认长度为4个字节。
#### 4.2.9 **DOUBLE :*
* 浮点数。长度为8个字节。
> [!note]
> NULL 字符串可以使用以下方式指定。如果省略之后又要导入 NULL 值。 NULL 将会被记录在 `PARSE_BADFILE`。
#### 4.2.10 **NULLIF 'null_string' :**
指定 `CHAR` 或者 `VARCHAR` 表示 `NULL` 的字符串。字符串的长度必须与类型的长度相同。
#### 4.2.11 **NULLIF null_hex :**
指定不是 `CHAR` 和 `VARCHAR` 表示 `NULL` 的十六进制值。十六进制的值的长度必须与类型的长度相同。
## 5 Binary 导入参数
### 5.1 COL 参数格式
{{CODE_BLOCK_29}}
### 5.2 COL 参数说明
对导入文件的每一列的定义，定义包括：类型名称、偏移量和长度（以字节为单位）组成。
`CHAR` 和 `VARCHAR` 表示导入文本数据。否则，就是二进制数据。如果二进制数据，服务器和数据文件的大小端必须一致。
#### 5.2.1 **CHAR | CHARACTER :**
指定截断尾部空格后的字符串。
#### 5.2.2 **VARCHAR | CHARACTER VARYING :**
指定保留尾部空格的字符串。
#### 5.2.3 **SMALLINT | SHOFT :**
2个字节的有符号整数。
#### 5.2.4 **INTEGER | INT :**
有符号整数，可以为2个字节，4个字节，8个自己。默认为4 个字节。
#### 5.2.5 **BIGINT | LONG :**
8个字节的有符号整数。
#### 5.2.6 **UNSIGNED SMALLINT | SHORT :**
2 个字节的无符号整数。
#### 5.2.7 **UNSIGNED INTEGER | INT :**
无符号整数，可以为2 个字节，4个字节无符号整数。默认为4.
#### 5.2.8 **FLOAT | REAL :**
浮点数，可以为4个字节或8个字节。默认为4。
#### 5.2.9 **DOUBLE :**
浮点数，可以为8个字节。
### 5.3 指定类型长度和偏移量
类型的长度和偏移量可以按照以下方式指定：
- **TYPE :** TYPE with default length follows.
- **TYPE(L) :** TYPE with L bytes follows.
- **TYPE(S+L) :** L bytes, offset S bytes from the beginning of the line
- **TYPE(S:E) :** start at S bytes and end at E bytes.
> [!note]
> 可以使用以下方式指定NULL 字符串。
#### 5.3.1 **NULLIF 'null_string' :**
指定 `CHAR` 或者 `VARCHAR` 表示 `NULL` 的字符串。字符串的长度必须与类型的长度相同。
#### 5.3.2 **NULLIF null_hex :**
指定不是 `CHAR` 和 `VARCHAR` 表示 `NULL` 的十六进制值。十六进制的值的长度必须与类型的长度相同。
> [!note]
> 此外，还提供了 `COL N` ，与 `COL CHAR(N)` 相同。以便向后兼容。
此参数设置为YES时，将 `COL N` 视为 `COL CHAR(N)`。此参数设置为 `NO` 时视为 `COL VARCHAR(N)`。
{{CODE_BLOCK_30}}
此参数设置一行的长度，用于保证一行不会过大。
{{CODE_BLOCK_31}}
## 6 参考链接
- [pg_bulkload (ossc-db.github.io)](https://ossc-db.github.io/pg_bulkload/pg_bulkload.html#controlfile)